OneDrive コネクタでデータファイルを Snowflake に同期してみた #Fivetran

OneDrive コネクタでデータファイルを Snowflake に同期してみた #Fivetran

Clock Icon2024.08.27

はじめに

Fivetran では、ファイル転送用のコネクタとして OneDrive コネクタを提供しています。OneDrive コネクタでデータファイルを Destination(Snowflake)に同期してみましたので、その際の手順を記事としました。

OneDrive コネクタ

OneDrive コネクタについては、以下に記載があります。

https://www.fivetran.com/connectors/onedrive

https://fivetran.com/docs/connectors/files/one-drive/setup-guide

コネクタの特徴

下図は公式ドキュメントからの引用ですが、削除のキャプチャや履歴モード、カラムのハッシュ化など一部対応していない項目もあるためご注意ください。

image

同期の概要

OneDrive コネクタでは、コネクタ作成時に指定したフォルダ内のデータファイルを同期します。この際の主な特徴は以下です。

  • 同期はマジック フォルダー モードで行われます
    • このモードでは、各データファイルが宛先に一意のテーブルとして同期されます
      • 同じ列構成のデータであっても、ファイルが異なる場合は別のテーブルとして作成されます
  • Excel の場合は、各シートで異なるテーブルとして同期されます
    • ただし、以下の場合は同期されません
      • 空のワークシート
      • ワークシートの最初の行全体が空、最初の行が空のカラム
  • 指定のフォルダ内にネストされたフォルダがある場合は無視されます
    • この場合は別にコネクタを作成する必要があります
  • OneDrive コネクタでは増分同期がサポートされていません
    • 前回の同期から変更があった(更新日時に変更があった)ファイルが次回の同期対象となります
    • 同期毎に変更があったファイルのみを再インポートされます
  • サポートされるファイルフォーマットとエンコーディング

宛先テーブル名

  • 拡張子を除くファイル名で、コネクタ作成時に指定のスキーマにテーブルが作成されます
    • 例:sample.csv の場合SAMPLEの名称でテーブルが作成される
  • Excel の場合、ファイル名とシート名の組み合わせが宛先テーブル名として使用されます
    • 例:My Workings.xlsx内にSheet1Sheet2がある場合、以下のテーブルが作成されます
      • MY_WORKINGS_SHEET_1
      • MY_WORKINGS_SHEET_2
    • 命名規則の詳細はこちらをご参照ください

制約

  • データファイルが Excel の場合、以下の拡張子がサポートされています
    • .xls,.xlsx,.xlsm
  • ワークシートのハイパーリンク値は同期されません
  • ワークシート上のピボットテーブルは同期されません

検証条件

ここでは以下の環境を使用しました。

  • Microsoft 365(個人の無料プラン)

https://www.microsoft.com/ja-jp/microsoft-365/onedrive/online-cloud-storage

  • Destination
    • Snowflake

事前準備

Onedrive 側

Fivetran で同期対象となるデータファイルを格納するためのフォルダを作成しておきます。

image 1

Destinaiton

データの連携先には Snowflake を使用しました。Snowflake を Destination に設定する方法は、以下の記事をご参照ください。

https://dev.classmethod.jp/articles/fivetran-destination-setting-snowflake/

Fivetran の設定

データファイルの用意

Snowflake に同期するサンプルのデータファイルとして3つのシートにデータのある Excel ファイル(SampleECData.xlsx)を用意しました。

  • Orders シート

image 2

  • Customers シート

image 3

  • Products シート

image 4

このファイルを事前準備で作成した OneDrive 上のフォルダに追加しておきます。

image 5

コネクタの作成

Fivetran ダッシュボードから OneDrive コネクタを選択し Destination を指定します。

image 6

設定画面は下図のようになっています。

image 7

設定項目は以下です。

  • Destination schema
    • テーブルの作成先となるスキーマ名
  • Folder URL
    • OneDrive 側でデータファイルの保存先となるフォルダをブラウザで開いた際に表示されるURL

image 8

項目を入力後 [Authorize] をクリックします。必要に応じて対象の Microsoft アカウントにサインインし、アクセスを許可します。問題なければ下図の表示となるので、設定を保存し接続テストを行います。

image 9

テストが完了すると初期同期を開始できるので、[Start Initial Sync] をクリックします。

image 10

同期後、Snowflake 側を確認すると指定のスキーマ配下に命名規則に基づいてテーブルが作成されます。Excel ファイルでシートごとにデータがある場合は、シートごとにテーブルが作成されます。

image 11

データを確認すると Fivetran 側でキー列を識別する_LINE_FIVETRAN_SYNCEDのシステム列が追加される形で同期されていました。

image 12

データファイルの変更

既存ファイルの変更

変更対応として、既存のデータファイルの中身を更新し再度同期してみます。ここでは以下の変更を行います。

  • Customers シート
    • [contact_email] カラムを削除
    • レコードを2件追加

image 13

  • Orders シート
    • [new_column] カラムを追加
    • [id]=10 の [quantity] カラムの値を更新

image 14

変更後ファイルを上書き保存し、再度同期を実行し Snowflake 側で確認します。

  • CUSTOMERS テーブル
    • Excel 側で削除したカラムの値に NULL が入る形で更新されます
    • 新規レコードの追加も確認できます

image 15

  • ORDERS テーブル
    • 値の更新と新しいカラムの追加も確認できます

image 16

また、ここでは記載していませんが、ソース側でレコードを削除した場合、再インポートのため論理削除ではなくレコード削除後のテーブルとして生成されます。_LINEカラムも新たにレコード順にふり直されます。

再インポートの確認

上記までの状態から、さらに Orders シートにレコードを追加し同期を行います。

image 17

同期完了後、Snowflake 側でレコードの追加を確認できます。

image 18

ここで_FIVETRAN_SYNCED カラムを見ると変更のない既存レコードもこの値が更新されていることが確認できます。他にも、変更を加えていない PRODUCTS テーブルについてもほぼ同時刻に更新されたことが確認できます。

image 19

同期の概要でも記載している通り、OneDrive コネクタは増分同期をサポートしておらず、変更があったファイルレベルで再度インポートを行います。そのため、変更があったシート内の変更がなかったレコードや、そもそも変更がなかったシートであっても各レコードの更新日時が最新化されています。

再インポートによる更新の場合、増分更新よりも多くの MAR が発生するためご注意ください。

https://fivetran.com/docs/getting-started/glossary#reimport

ファイルの追加

既存ファイルとは別の以下のファイルをそれぞれ追加して同期を行ってみます。

csv_sample.csv
ProductID,ProductName,Category,Price,StockQuantity
101,Apple,Fruit,0.50,150
102,Banana,Fruit,0.30,200
103,Orange,Fruit,0.60,180
104,Broccoli,Vegetable,1.20,100
105,Carrot,Vegetable,0.80,120
106,Chicken,Meat,5.00,50
107,Beef,Meat,8.50,40
108,Milk,Dairy,1.50,80
109,Cheese,Dairy,2.50,60
110,Bread,Bakery,2.00,100
json_sample.json
{
    "employees": [
        {
            "employee_id": 1,
            "first_name": "John",
            "last_name": "Doe",
            "department": "Engineering",
            "email": "[email protected]",
            "salary": 75000,
            "is_full_time": true
        },
        {
            "employee_id": 2,
            "first_name": "Jane",
            "last_name": "Smith",
            "department": "Marketing",
            "email": "[email protected]",
            "salary": 68000,
            "is_full_time": true
        },
        {
            "employee_id": 3,
            "first_name": "Emily",
            "last_name": "Johnson",
            "department": "Sales",
            "email": "[email protected]",
            "salary": 62000,
            "is_full_time": false
        }
    ]
}
  • parquet_sample.parquet
    • iris データセットのカラム名のドットをアンダースコアとし parquet 形式に変換したもの

image 20

同期後、Snowflake で確認します。

  • csv_sample.csv

image 21

  • json_sample.json

image 22

  • parquet_sample.parquet

image 23

日本語のファイル名やカラム名を含む場合の注意点

以下のような日本語のカラム名からなる顧客リスト.csv というデータファイルを同期するとします。

顧客リスト.csv
顧客ID,名前,メールアドレス,電話番号,住所,購入回数
1,佐藤太郎,[email protected],080-1234-5678,東京都新宿区1-1-1,5
2,鈴木花子,[email protected],080-2345-6789,大阪府大阪市2-2-2,3
3,高橋一郎,[email protected],080-3456-7890,愛知県名古屋市3-3-3,8
4,田中美咲,[email protected],080-4567-8901,福岡県福岡市4-4-4,2
5,伊藤健一,[email protected],080-5678-9012,北海道札幌市5-5-5,6

同期後、Snowflake で確認すると下図のようになります。テーブル名とカラム名が一部意図するものとはなっていません。

image 24

これはこのコネクタに限らず、以下に記載がある Fivetran 側の仕様によるもので、非 ASCII 文字は音訳に置き換えられためです。この際、特に漢字を含む場合は日本語として意図する形で変換されないため、予め英語形式のカラム名を用意しておくか、DWH 内での整形が必要です。

https://fivetran.com/docs/connectors/databases#mappingnamefromsourcetodestination

さいごに

Fivetran の OneDrive コネクタでデータファイルを Snowflake に同期してみました。再インポートによる注意点はありますが、Excel などのデータファイルも手軽に連携できる点は強みだと思います。こちらの内容が何かの参考になれば幸いです。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.